import sqlite3

# 连接到 SQLite 数据库（如果不存在则会创建）
conn = sqlite3.connect('boss.db')
cursor = conn.cursor()

status_enum = {
    0: '投递失败',
    1: '投递成功',
}

def table_init():
    # 创建表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS black_list (
        id INTEGER PRIMARY KEY,
        compony_name TEXT NOT NULL
    )
    ''')
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS send_history (
        id INTEGER PRIMARY KEY,
        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        job_name TEXT NOT NULL,
        compony_name TEXT NOT NULL,
        status INTEGER NOT NULL
    )
    ''')

# ====================
# 黑名单表 SDK
# ====================
class BlackListSDK:
    @staticmethod
    def add_company(name):
        """添加公司到黑名单"""
        try:
            cursor.execute("INSERT INTO black_list (compony_name) VALUES (?)", (name,))
            conn.commit()
            return cursor.lastrowid
        except sqlite3.Error as e:
            conn.rollback()
            print(f"添加黑名单失败: {e}")
            return None

    @staticmethod
    def remove_company(compony_name):
        """从黑名单移除公司"""
        try:
            cursor.execute("DELETE FROM black_list WHERE compony_name = ?", (compony_name,))
            conn.commit()
            return cursor.rowcount
        except sqlite3.Error as e:
            conn.rollback()
            print(f"移除黑名单失败: {e}")
            return 0


    @staticmethod
    def list_companies():
        """获取所有黑名单公司"""
        cursor.execute("SELECT * FROM black_list")
        return cursor.fetchall()

# ====================
# 投递历史表 SDK
# ====================
class SendHistorySDK:
    @staticmethod
    def add_record(job_name, company_name, status):
        """添加投递记录"""
        try:
            cursor.execute(
                "INSERT INTO send_history (job_name, compony_name, status) VALUES (?, ?, ?)",
                (job_name, company_name, status)
            )
            conn.commit()
            return cursor.lastrowid
        except sqlite3.Error as e:
            conn.rollback()
            print(f"添加投递记录失败: {e}")
            return None

    @staticmethod
    def get_record(job_name=None, company_name=None, status=None, timestamp=None):
        """获取符合条件的投递记录（支持前缀匹配）"""
        query = "SELECT * FROM send_history WHERE 1=1"
        params = []
        
        if job_name is not None:
            query += " AND job_name LIKE ?"
            params.append(f"{job_name}%")
            
        if company_name is not None:
            query += " AND compony_name LIKE ?"
            params.append(f"{company_name}%")
            
        if status is not None:
            query += " AND status = ?"
            params.append(status)
            
        if timestamp is not None:
            query += " AND timestamp >= ?"
            params.append(timestamp)
        
        cursor.execute(query, params)
        return cursor.fetchall()

    @staticmethod
    def list_records(limit=None, offset=None):
        """获取所有投递记录"""
        query = "SELECT * FROM send_history"
        if limit is not None:
            query += f" LIMIT {limit}"
        if offset is not None:
            query += f" OFFSET {offset}"

        cursor.execute(query)
        return cursor.fetchall()